//
//  DBManager.swift
//  BookStore2.0
//
//  Created by hw on 2022/6/3.
//
import Foundation
import UIKit
class DB1{
    static let url=URL(string: "http://zy.whu.edu.cn/cs/api/book/list")!
    static let DBManager=DB2.sharedInstance
    static var BookBasicInfo:[[String:AnyObject]]?
    static var BookBasicInfoDictionary=[String:[[String:AnyObject]]]()
    static var kinds = [String]()
    static var imgSet=[String:UIImage]()
    static func downloadBookInfo()
    {
        let task=URLSession.shared.dataTask(with: url)
        {
            (data,response,error) in
            if let data=data,
               let jsonObject=try? JSONSerialization.jsonObject(with: data, options: .mutableLeaves),
               let jsn=jsonObject as?[String:Any]
            
            {
                let bookInfo=jsn["result"] as! [[String:Any]]
                createDB()
                DispatchQueue.main.async
                {
                    for Abook in bookInfo
                    {
                        let id=Abook["id"] as! String
                        let title=Abook["title"] as! String
                        let description=Abook["description"] as! String
                        let kind=Abook["kind"] as! String
                        let price1=Abook["price"] as! String
                        let price=Float(price1)!
                        let author=Abook["author"] as! String
                        let publisher=Abook["publisher"] as!String
                        let imgUrl = Abook["pic"] as! String
                        insertBookInfo(id: id , title: title , description: description , kind: kind, price: price, author: author, publisher: publisher,imgUrl:imgUrl)
                        downloadImg(url:imgUrl,bookid: id)
                    }
                    createView()
                    selectkind()
                    getBookInfoDictionary()
                    ShoppingCart.sharedinstance.importBookCopy()
                    
                }
            }
        }
        task.resume()
    }
    static func downloadImg(url:String,bookid:String)
    {
            let urlNet=URL(string: url)
            let task=URLSession.shared.dataTask(with: urlNet!)
            {
                (data,response,error) in
                if let nsd=data{
                    DispatchQueue.main.async {
                        imgSet[bookid]=UIImage(data: nsd,scale: 1)
                    }
                }
                
            }
            task.resume()
    }
    static func getBookInfoDictionary()
    {
        for kind in kinds {
            selectBookBasicInfo(akind: kind)
            BookBasicInfoDictionary[kind]=BookBasicInfo
        }
    }
    // MARK: 创建表
    static func createDB()
    {
        if !DBManager.openDB() {
            return
        }
        print(DBManager.dbPath!)
        let SQL_createDB_Book="create table if not exists Book('id' text not null primary key,'title' text,'description' text,'kind' text,'price' float,'author' text,'publisher' text,'imgUrl' text,'pic' BLOB)"
        
        let SQL_createDB_BookCopy="create table if not exists BookCopy('id' text not null primary key ,'title' text,'amount' int,'eachprice' float)"
        
        let SQL_createDB_OrderItem="create table if not exists OrderItem('id' integer not null primary key autoincrement,'bookid' text,'title' text,'amount' int,'cost' float,'orderid' int)"
        
        let SQL_createDB_Order="create table if not exists Orders('id' integer not null primary key autoincrement,'booknumSum' int,'moneySum' float,'orderTime' text )"
        if !DBManager.execNoneQuerySQL(sql: SQL_createDB_Book) {
            DBManager.closeDB();return
        }
        
        if !DBManager.execNoneQuerySQL(sql: SQL_createDB_BookCopy){
            DBManager.closeDB();return
        }
        if !DBManager.execNoneQuerySQL(sql: SQL_createDB_OrderItem)
        {
            DBManager.closeDB();return
        }
        if !DBManager.execNoneQuerySQL(sql: SQL_createDB_Order){
            DBManager.closeDB();return
        }
        DBManager.closeDB()
    }
    static func createView()
    {
        if  !DBManager.openDB() {
            return
        }
        
        let SQL_createBookBasicInfoView = "create view if not exists BookBasicInfoView as select id,title,price,imgUrl,kind from Book"
        
        if !DBManager.execNoneQuerySQL(sql: SQL_createBookBasicInfoView){
            DBManager.closeDB(); return
        }
        DBManager.closeDB();
        
    }
   
    static func selectkind()
    {
        if !DBManager.openDB(){return}
        let SQL_selectKind="select kind from Book group by kind"
        let result=DBManager.execQuerySQL(sql: SQL_selectKind)
        for resultItem in result!
        {
            //print(resultItem["kind"] as! String
            kinds.append(resultItem["kind"] as! String)
            //print(kinds)
        }
        DBManager.closeDB()
    }
    static func selectBookByid(id:String) -> [[String:AnyObject]]?
    {
        if !DBManager.openDB(){
            return nil
        }
        let SQL_selectBookByid="select id,title,description,kind,price,author,publisher,imgUrl from Book where id='\(id)'"
        
        let  Abook=DBManager.execQuerySQL(sql: SQL_selectBookByid)
        DBManager.closeDB()
        return Abook
    }
    static func selectBookBasicInfo( akind:String)
    {
        if  !DBManager.openDB() {
            return
        }
        let SQL_selectBookBasicInfo = "select * from BookBasicInfoView where kind='\(akind)'"
        BookBasicInfo=DBManager.execQuerySQL(sql: SQL_selectBookBasicInfo)
        DBManager.closeDB();
    }
    
    static func selectAllBookCopy()->[[String:AnyObject]]?
    {
        if  !DBManager.openDB() {
            return nil
        }
        let SQL_selectAllBookCopy = "select * from BookCopy"
        let BookCopies=DBManager.execQuerySQL(sql: SQL_selectAllBookCopy)
        DBManager.closeDB();
        return BookCopies
    }
    static func selectAllOrder()->[[String:AnyObject]]?
    {
        if  !DBManager.openDB() {
            return nil
        }
        let SQL_selectAllBookCopy = "select * from Orders"
        let orders=DBManager.execQuerySQL(sql: SQL_selectAllBookCopy)
        DBManager.closeDB()
        return orders
    }
    
    static func selectOrderItemByOrderID(orderID:Int)->[[String:AnyObject]]?
    {
        if !DBManager.openDB(){
            return nil
        }
        let SQL_selectByOrderID="select * from OrderItem where orderid=\(orderID)"
        let result=DBManager.execQuerySQL(sql: SQL_selectByOrderID)
        DBManager.closeDB()
        return result
    }
    static func saveImage(id:String,db:String,img:UIImage?)
    {
        if img==nil{return}
        if !DBManager.openDB(){return}
        let SQL_updatePic="update '\(db)' set pic=? where id='\(id)'"
        let data=img!.jpegData(compressionQuality: 1.0) as NSData?
        DBManager.execSaveBlob(sql: SQL_updatePic, blob: data!)
        DBManager.closeDB()
        return
    }
    static func insertBookInfo(id:String,title:String,description:String,kind:String,price:Float,author:String,publisher:String,imgUrl:String)
    {
        if !DBManager.openDB(){return}
        
        let SQL_insertBookInfo = "insert or replace into Book(id,title,description,kind,price,author,publisher,imgUrl)values('\(id)','\(title)','\(description)','\(kind)',\(price),'\(author)','\(publisher)','\(imgUrl)')"
        if !DBManager.execNoneQuerySQL(sql: SQL_insertBookInfo){DBManager.closeDB();return}
        DBManager.closeDB()
        
    }
    
    static func insertBookCopy(bookcopy:BookCopy)
    {
        let id=bookcopy.id
        let amount=bookcopy.amount
        let title=bookcopy.title
        let eachprice=bookcopy.eachprice
        let SQL_insertBookCopy="insert or replace into BookCopy(id,title,amount,eachprice) values('\(id)','\(title)',\(amount),\(eachprice))"
        if !DBManager.openDB(){return}
        if !DBManager.execNoneQuerySQL(sql: SQL_insertBookCopy){DBManager.closeDB();return}
        DBManager.closeDB()
    }
    static func insertOrder(order:Order) -> [[String:AnyObject]]?
    {
        let booknumSum=order.booknumSum
        let moneySum=order.moneySum
        let dateFormater=DateFormatter()
        dateFormater.dateFormat="yyyy-MM-dd HH:mm:ss"
        dateFormater.locale=Locale(identifier: "zh_CN")
        let datetext=dateFormater.string(from: order.ordertime!)
        
        let SQL_insertOrder = "insert or replace into Orders(booknumSum,moneySum,orderTime) values(\(booknumSum),\(moneySum),'\(datetext)')"
        let SQL_selectOrderID="select max(rowid) from Orders"
        if !DBManager.openDB(){return nil}
        if !DBManager.execNoneQuerySQL(sql: SQL_insertOrder){DBManager.closeDB();return nil}
        let result=DBManager.execQuerySQL(sql: SQL_selectOrderID)
        DBManager.closeDB()
        return result
    }
    static func insertOrderItem(bookcopy:BookCopy,orderid:Int)
    {
        let bookid=bookcopy.id
        let amount=bookcopy.amount
        let title=bookcopy.title
        let eachprice=bookcopy.eachprice
        let cost=eachprice*Float(amount)
        
        let SQL_insertBookCopy="insert or replace into OrderItem(bookid,title,amount,cost,orderid) values('\(bookid)','\(title)',\(amount),\(cost),\(orderid))"
        if !DBManager.openDB(){return}
        if !DBManager.execNoneQuerySQL(sql: SQL_insertBookCopy){DBManager.closeDB();return}
        DBManager.closeDB()
        
    }
    static func deleteBookCopy(id:String)
    {
        
        let SQL_deleteBookCopy="delete from BookCopy where id='\(id)'"
        if !DBManager.openDB(){return}
        if !DBManager.execNoneQuerySQL(sql: SQL_deleteBookCopy){DBManager.closeDB();return}
        DBManager.closeDB()
        
    }
    static func clearBookCopy()
    {
        let SQL_deleteAllBookCopy="delete from BookCopy"
        if !DBManager.openDB(){return}
        if !DBManager.execNoneQuerySQL(sql: SQL_deleteAllBookCopy){DBManager.closeDB();return}
        DBManager.closeDB()
    }
}
class DB2:NSObject
{
    public var dbPath:String!
    public var database:OpaquePointer? = nil
    static var sharedInstance:DB2
    {
        return DB2()
    }
    override init()
    {
        super.init()
        let dirpath = FileManager.default.urls(for:.documentDirectory,in:.userDomainMask).first!
        dbPath = dirpath.appendingPathComponent("BOOKSTORE2.0.sqlite").path
        print(dbPath!)
    }
    func openDB()->Bool{
        let result = sqlite3_open(dbPath,&database)
        if result != SQLITE_OK
        {
            print("fail to open database")
            return false
        }
        return true
    }
    func closeDB()
    {
        sqlite3_close(database)
    }
    func execNoneQuerySQL(sql:String)->Bool
    {
        
        var errMsg:UnsafeMutablePointer<Int8>? = nil
        let cSql = sql.cString(using:String.Encoding.utf8)!
        if sqlite3_exec(database, cSql, nil, nil, &errMsg) == SQLITE_OK
        {
            return true
        }
        let msg = String.init(cString:errMsg!)
        print(msg)
        return false
    }
    func execQuerySQL(sql:String) -> [[String:AnyObject]]?
    {
        let cSql = sql.cString(using: String.Encoding.utf8)!
        var statement:OpaquePointer? = nil
        if sqlite3_prepare_v2(database, cSql, -1, &statement, nil) != SQLITE_OK
        {
            sqlite3_finalize(statement)
            print("执行\(sql)错误\n")
            let errmsg = sqlite3_errmsg(database)
            if errmsg != nil{
                print(errmsg!)
            }
            return nil
        }
        var rows = [[String:AnyObject]]()
        while sqlite3_step(statement) == SQLITE_ROW
        {
            rows.append(record(stmt:statement!))
        }
        sqlite3_finalize(statement)
        return rows
    }
    func execSaveBlob(sql:String,blob:NSData)
    {
        let csql=sql.cString(using:.utf8)!
        var statement:OpaquePointer?=nil
        if sqlite3_prepare(database,csql, -1, &statement, nil) != SQLITE_OK
        {
            sqlite3_finalize(statement)
            print("Prepare error:\(sql)")
            return
        }
        let paramCnt=sqlite3_bind_parameter_count(statement)
        if paramCnt != 1
        {
            print("need only 1 parameter:\(sql)")
            sqlite3_finalize(statement)
            return
        }
        if sqlite3_bind_blob(statement,1,blob.bytes,Int32(blob.length),nil) != SQLITE_OK
        {
            print("bind blob error:\(sql)")
            sqlite3_finalize(statement)
            return
        }
        let result=sqlite3_step(statement)
        
        if result != SQLITE_OK && result != SQLITE_DONE
        {
            print("execute blob error:\(sql)")
            sqlite3_finalize(statement)
            return
            
        }
        sqlite3_finalize(statement)
        
    }
    func  execLoadBlob(sql:String) -> Data? {
        let csql = sql.cString(using: String.Encoding.utf8)!
        var statement:OpaquePointer? = nil
        if sqlite3_prepare_v2(database,csql,-1,&statement,nil) != SQLITE_OK
        {
            sqlite3_finalize(statement)
            print("执行\(sql)错误\n")
            if let errmsg=sqlite3_errmsg(database)
            {
                print(errmsg)
            }
            return nil
        }
        while sqlite3_step(statement) == SQLITE_ROW
        {
            if let dataBlob = sqlite3_column_blob(statement, 0)
            {
                let dataBlobLength=sqlite3_column_bytes(statement, 0)
                let data = Data(bytes:dataBlob, count:Int(dataBlobLength))
                sqlite3_finalize(statement)
                return data
            }
        }
        sqlite3_finalize(statement)
        return nil
    }

    func record(stmt:OpaquePointer)->[String:AnyObject]
    {
        var row = [String:AnyObject]()
        for col in 0..<sqlite3_column_count(stmt)
        {
            let cName = sqlite3_column_name(stmt, col)
            let name = String(cString: cName!, encoding: String.Encoding.utf8)
            var value:AnyObject?
            switch (sqlite3_column_type(stmt, col))
            {
            case SQLITE_FLOAT://浮点类型
                value = sqlite3_column_double(stmt, col) as AnyObject ;break
            case SQLITE_INTEGER://整数类型
                value = sqlite3_column_int(stmt, col) as AnyObject ;break
            case SQLITE_TEXT://文本类型
                let cText = sqlite3_column_text(stmt, col)
                value = String.init(cString:cText!) as AnyObject ;break
            case SQLITE_NULL://空
                value = NSNull() ;break
            default:
                print("不支持的数据类型")
            }
            row[name!] = value ?? NSNull()
        }
        return row
    }
}
